<?php

/**
 * group model 文件
 * @author lijie
 * @date 2016-02-15
 */
class M_quantification_User extends MY_Model {
	
		public $table = "t_quantification_user";
	    function M_group() {
	        parent::__construct();
	    }
        //干部网格巡视详情
	    public function get_leader($dept_id,$where,$type=0,$limit='',$page=0)
        {
            if($type== 0)
            {
                $sql ="select count(um.user_id)as num,count(IF(um.status=2,true,null)) as wnum,u.user_name from t_quantification_user_map um LEFT JOIN t_quantification_user us on um.quantification_user_id=us.quantification_user_id LEFT JOIN t_user u ON um.user_id=u.user_id LEFT JOIN t_department d ON u.department_id=d.department_id where us.quantification_user_status=1 $where and u.department_id in($dept_id) AND um.user_id<>3008 GROUP BY um.user_id";
            }
            else
            {

                if($page==1)
                {
                    $sql ="select count(*) as count
                        FROM
                        t_quantification_user_map um
                        LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id
                        LEFT JOIN t_user u ON um.user_id = u.user_id
                        WHERE us.quantification_user_status = 1 $where";
                }
                elseif($page == 2)
                {
                    $sql = "SELECT
                                    u.user_account,
                                    u.user_id,
                                    d.department_name,
                                    SUM(um.finish_number) sum_end_number,
                                    SUM(um.number) sum_number,
                                    u.user_name
                                FROM
                                    t_quantification_user_map um
                                LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id
                                LEFT JOIN t_user u ON um.user_id = u.user_id
                                LEFT JOIN t_department d ON u.department_id = d.department_id
                                WHERE
                                    us.quantification_user_status = 1 $where
                                GROUP BY u.user_id
                                ORDER BY u.user_id DESC $limit";
                }
                elseif($page == 3)
                {
                    $sql = "select count(*) as count from
                    (SELECT
                                    u.user_account,
                                    u.user_id,
                                    d.department_name,
                                    SUM(um.finish_number) sum_end_number,
                                    SUM(um.number) sum_number,
                                    u.user_name
                                FROM
                                    t_quantification_user_map um
                                LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id
                                LEFT JOIN t_user u ON um.user_id = u.user_id
                                LEFT JOIN t_department d ON u.department_id = d.department_id
                                WHERE
                                    us.quantification_user_status = 1 $where
                                GROUP BY u.user_id) t1";
                }
                else
                {
                    $sql ="select t1.*,t2.addtime from
                        (
                                SELECT
                                    um.quantification_user_map_id,
                                    u.user_account,
                                    q.quantification_name AS quname,
                                    u.user_id,
                                    d.department_name,
                                    um.finish_number,
                                    map.grid_name,
                                    um.number,
                                    um.start_time,
                                    um.end_time,
                                    u.user_name
                                FROM
                                    t_quantification_user_map um
                                LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id
                                LEFT JOIN t_quantification q ON us.quantification_id = q.quantification_id
                                LEFT JOIN t_user u ON um.user_id = u.user_id
                                LEFT JOIN t_department d ON u.department_id = d.department_id
                                LEFT JOIN t_grid map ON map.grid_id = um.grid_id
                                WHERE
                                    us.quantification_user_status = 1 $where
                                GROUP BY um.quantification_user_map_id
                                ORDER BY um.quantification_user_map_id DESC
                                    ) t1
                                LEFT JOIN
                                 (
                                    SELECT
                                            um.quantification_user_map_id,
                                            MAX(addss.add_time) as addtime
                                    FROM
                                        t_quantification_user_map_add addss
                                    LEFT JOIN t_quantification_user_map um ON addss.quantification_user_map_id = um.quantification_user_map_id
                                        LEFT JOIN t_quantification_user uss ON um.quantification_user_id = uss.quantification_user_id
                                        LEFT JOIN t_user u ON um.user_id = u.user_id
                                    WHERE
                                        uss.quantification_user_status=1 $where
                                GROUP BY um.quantification_user_map_id
                                ORDER BY um.quantification_user_map_id DESC
                                ) t2
                                ON t1.quantification_user_map_id=t2.quantification_user_map_id
                                GROUP BY t1.quantification_user_map_id
                                ORDER BY t2.addtime desc $limit";
                }
            }
            $result = $this->querylist($sql);
            return $result;
        }
        //职工网格巡视详情
        public function get_worker($start,$end,$where,$type=0,$limit='',$page=0)
        {
            if($type==0)
            {
                $sql ="SELECT count(um.user_id) AS num, count(IF(um. STATUS = 2, TRUE, NULL)) AS wnum, u.user_name FROM t_quantification_user_map um LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id LEFT JOIN t_user u ON um.user_id = u.user_id LEFT JOIN t_department d ON u.department_id = d.department_id WHERE us.quantification_user_status = 1 $where GROUP BY um.user_id";
            }
            else
            {
                $sql ="select u.user_account,u.user_id,um.status,if(q.quantification_post_type=3,w.post_name,'') as gname,ww.post_name,um.quantification_user_map_id,adds.add_time,q.quantification_name AS quname, adds.quantification_user_map_id as qum_id,d.department_name as buname, um.finish_number, map.grid_name, um.number, um.start_time, um.end_time, u.user_name 
                        from t_quantification_user_map um
                        LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id
                        LEFT JOIN t_quantification q ON us.quantification_id=q.quantification_id
                        LEFT JOIN t_quantification_user_map_add adds ON um.quantification_user_map_id = adds.quantification_user_map_id
                        LEFT JOIN t_user u ON um.user_id = u.user_id
                        LEFT JOIN t_department d ON u.department_id = d.department_id
                        LEFT JOIN t_grid map ON map.grid_id = um.grid_id 
                        LEFT JOIN t_post w ON q.quantification_user=w.post_id
                        LEFT JOIN t_post ww ON u.post_id=w.post_id
                        where us.quantification_user_status=1 $where
                        ORDER BY adds.add_time ";
                if($page==1)
                {
                    $sql ="select count(*) as count from ($sql) t1";
                }
                else
                {
                    $sql = $sql.$limit;
                }
            }
            $result = $this->querylist($sql);
            return $result;
        }

    //网格巡视情况统计
    public function get_wangge($start_time,$end_time,$where,$limit='',$type=0,$page=0,$status=0,$hours=2)
    {
        $h = $end_time-$start_time;
        $hs = date("H",$start_time);
        $he = date("H",$end_time);
        $dd =$hs%2;//0为偶数，1为基数
        $h1 = $h/3600;
        $h = $h/3600;
        if($h>=24)
        {
            $y = $h%24;//余数 23
            if($y>0)
            {
                $s =24-$y;//剩余小时
                $sy = intval($s/$hours);
                $d = intval($h/24);//天数
                $h=24/$hours;//一天必须完成的任务数
                $time_h = date('H',$end_time);//获取时
                $time_hy =$time_h-$y;
                $hour = 1;
                $tt=2;
                for($i=0;$i<$h;$i++)
                {
                    $hour1 = $hour;
                    if($hours ==2 && $hour1 == 1)//01点开始，并且hours为2
                    {
                        $hour = $hour+$hours;
                    }
                    else
                    {
                        $hour = $hour+$hours;
                    }
                    if($hour1<=$time_h && $time_h<$hour)
                    {
                        if(($hour1 <$time_h && $time_h<$hour) &&  ($hour1 <$time_hy && $time_hy<$hour))
                        {
                        }
                        else
                        {
                            if(intval($hour1)!=intval($time_h))
                            {
                                $tt=1;break;
                            }
                        }
                    }
                }
                if($tt == 2)
                {
                    if($y<$hours)
                    {
                        $sy = $h;
                    }
                }
                if($hours==2)
                {
                    $aa=0;
                    if(($hs>= 01 && 05>=$hs) || ($he<= 05 && 04<=$he) || ($hs<=01 && $he>=05))
                    {
                        if($hs>=01 && $hs<=5)
                        {
                            $a1 = 05-$hs;
                            if($y>$a1)
                            {
                                $y = $y/2;
                                $aa = $y>1 ?$y-1:$y;
                                if($hs!=01)
                                {
                                    $aa = intval($aa);
                                }
                                else
                                {
                                    $aa = ceil($aa);
                                }
                            }
                            else
                            {
                                $aa=1;
                                if($hs!=01)
                                {
                                    $aa=0;
                                }
                            }
                        }
                        else
                        {
                            $y = $y/2;
                            $aa = intval($y)-1;
                        }
                    }
                    else
                    {
                        $y = $y/2;
                        if($dd == 1)
                        {
                            $aa = ceil($y)+$aa;
                        }
                        else
                        {
                            $aa = intval($y)+$aa;
                        }
                    }
                    $h = $d*$h+$aa-$d;
                }
                else
                {
                    $h = $d*$h+$h-$sy;
                }
            }
            else
            {
                $h=intval($h/$hours);
                if($hours==2)
                {
//                        $d = intval($h/24);//天数
//                        $h=$h-1;
                    if(($hs>= 01 && 05>=$hs) || ($he<= 05 && 01<=$he) || ($hs<=01 && $he>=05))
                    {
                        $h = $h-1;
                    }
                    else
                    {
                        $h = $h-1;
                    }
                }
            }
        }
        else
        {
            $y = $h%$hours;
            if($hours==2 && $y>0 && $dd==1)
            {
                $h = intval($h/$hours)+1;
            }
            else if($y>0 && $hours!=2)
            {
                $h = intval($h/$hours)+1;
            }
            else
            {
                $h=intval($h/$hours);
            }
            if($hours==2)
            {
                if(($hs>= 01 && 05>=$hs) || ($he<= 05 && 01<=$he) || ($hs<=01 && $he>=05))
                {
                    if($he == 4 || $he==5)
                    {
                        $h = $h-1;
                    }
                    else
                    {
                        $h = $h-1;
                    }
                }
                else
                {
                    $h = $h-1;
                }
            }
        }
        //24小时内有$h个任务
//            if($hours!=2)
//            {
//                $h=$h/$hours;
//            }
//            else
//            {
//                $h=12;//暂定12，默认11，因为1-5点算2小时任务，但只生成一次
//            }
        $left = $status==2?'RIGHT':'RIGHT';
        $hoursw = " and m.quantification_task_cycle_d=0 AND m.quantification_task_cycle_h=$hours ";
        if($type == 0)
        {
            if($where!="")
            {
                $left= 'LEFT';
            }
            if($status ==1)
            {
                $status = " WHERE t4.num is null or t4.num=0 or t4.num<$h";
            }
            else if($status==2)
            {
                $status = " WHERE t4.num = $h";
            }
            else
            {
                $status='';
            }
            $sql ="select t4.*,if(t4.num>=$h,1,0) as status from
(select count(if(t3.number,true,null)) as num,t3.grid_id,t3.department_id,t3.department_name,t3.grid_name,t3.grid_uname,sum(t3.number) number,sum(t3.status_num) status_num,sum(t3.wwc) wwc,sum(t3.ywc) ywc from
(select t1.*,t2.start_time,IFNULL(t2.number,0) number,IFNULL(t2.status,0) as status_num,IFNULL(t2.wwc,0) as wwc,IFNULL(t2.ywc,0) as ywc from
(select map.grid_id,map.department_id,map.grid_name,map.grid_uname,d.department_name from t_grid map left join t_department d on map.department_id=d.department_id $where GROUP BY map.grid_id) t1
$left JOIN
(SELECT
	um.grid_id,um.start_time,sum(um.finish_number) AS number,count(um.status) as status,count(if(um.status=1,true,null)) as wwc,count(if(um.status=2,true,null)) as ywc
FROM t_quantification_user_map um
LEFT JOIN t_quantification_map m 
ON um.quantification_map_id=m.quantification_map_id
WHERE um.start_time >= $start_time AND um.start_time < $end_time $hoursw
GROUP BY
	um.grid_id,um.start_time) t2 ON t1.grid_id=t2.grid_id ) t3
GROUP BY t3.grid_id) t4
$status
 ";
//                $sql ="select map.id,map.department_id,map.grid_name,sum(um.finish_number) as number from t_grid map left join t_quantification_user_map um on map.id=um.grid_id where um.addtime >= $start_time AND um.addtime < $end_time $where GROUP BY map.id";
        }
        else if($type==1)
        {
            $hours = " AND m.quantification_task_cycle_d=0 AND m.quantification_task_cycle_h=$hours ";
            if($status ==1)
            {
                $status = " WHERE t3.addtime is null";
            }
            else if($status==2)
            {
                $status = " WHERE t3.addtime is not null";
            }
            else
            {
                $status='';
            }
            $status = '';
            $sql = "select t3.* from (SELECT t1.*, t2.* FROM ( SELECT um.quantification_user_map_id, u.user_account, map.grid_uname, map.grid_name, map.department_id, um.grid_id, u.user_id, um.start_time, um.end_time, IF (q.quantification_post_type = 3, w.post_name, '无') AS gname, u.user_name, d.department_name AS bname FROM t_quantification_user_map um LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id LEFT JOIN t_quantification q ON us.quantification_id = q.quantification_id LEFT JOIN t_user u ON um.user_id = u.user_id LEFT JOIN t_department d ON u.department_id = d.department_id LEFT JOIN t_post w ON q.quantification_user = w.post_id LEFT JOIN t_grid map ON um.grid_id = map.grid_id LEFT JOIN t_quantification_map m ON um.quantification_map_id = m.quantification_map_id WHERE um.start_time >= $start_time AND um.start_time < $end_time $where $hours ) t1 LEFT JOIN ( SELECT adds.quantification_user_map_id as qum_id, adds.add_time FROM t_quantification_user_map_add adds LEFT JOIN t_quantification_user_map um ON um.quantification_user_map_id = adds.quantification_user_map_id LEFT JOIN t_grid map ON um.grid_id = map.grid_id LEFT JOIN t_quantification_map m ON um.quantification_map_id=m.quantification_map_id WHERE um.start_time >= $start_time AND um.start_time < $end_time $where $hours ) t2 ON t1.quantification_user_map_id = t2.qum_id GROUP BY t1.quantification_user_map_id ) t3 $status";
            if($page==1)
            {
                $sql ="select count(*) as count from ($sql) t4";
            }
            else
            {
                $sql = $sql.$limit;
            }
        }
        else
        {
            $hours = " AND m.quantification_task_cycle_d=0 AND m.quantification_task_cycle_h=$hours ";
//                if($status ==1)
//                {
//                    $status = " WHERE t5.num = 0";
//                }
//                else if($status==2)
//                {
//                    $status = " WHERE t5.num > 0";
//                }
//                else
//                {
//                    $status='';
//                }
            $status='';
            $sql = "select t5.* from (select sum(t3.finish_number) as num,t3.* from (SELECT t1.*, t2.* FROM ( SELECT um.quantification_user_map_id,um.finish_number, map.grid_uname, map.grid_name, map.department_id, d.department_name, um.grid_id, um.start_time, um.end_time FROM t_quantification_user_map um LEFT JOIN t_quantification_user us ON um.quantification_user_id = us.quantification_user_id LEFT JOIN t_quantification q ON us.quantification_id = q.quantification_id LEFT JOIN t_post w ON q.quantification_user = w.post_id LEFT JOIN t_grid map ON um.grid_id = map.grid_id LEFT JOIN t_quantification_map m ON um.quantification_map_id = m.quantification_map_id left join t_department d on map.department_id=d.department_id WHERE um.start_time >= $start_time AND um.start_time < $end_time $where $hours ) t1 LEFT JOIN ( SELECT adds.quantification_user_map_id qum_id, adds.add_time FROM t_quantification_user_map_add adds LEFT JOIN t_quantification_user_map um ON um.quantification_user_map_id = adds.quantification_user_map_id LEFT JOIN t_grid map ON um.grid_id = map.grid_id LEFT JOIN t_quantification_map m ON um.quantification_map_id=m.quantification_map_id WHERE um.start_time >= $start_time AND um.start_time < $end_time $where $hours ) t2 ON t1.quantification_user_map_id = t2.qum_id GROUP BY t1.quantification_user_map_id ) t3 GROUP BY t3.start_time) t5 $status";

        }
        $result = $this->querylist($sql);
        return $result;
    }
        //获取所有顶级部门
        public function get_dept_max()
        {
            $sql = "select ID,Name,ParentID from t_department where ParentID=0";
            $res = $this->querylist($sql);
            return $res;
        }
        //获取所有网格
        public function get_all_map()
        {
            $sql = "select id,area from t_grid";
            $res = $this->querylist($sql);
            return $res;
        }
        //根据部门获取所有人员
        public function get_pid($dept_id)
        {
            $sql = "select user_id from t_user where department_id in($dept_id)";
            $res = $this->querylist($sql);
            return $res;
        }
        /**
         * 检测该分类下所有子分类，并输出ID
         */
        public function getChildrenIds($sort_id,$type=0){
            $ids = $sort_id;
            if($type == 1)
            {
                $sql = "SELECT department_id FROM t_department WHERE workshop_id = 1";
            }
            else
            {
                $sql = "SELECT department_id FROM t_department WHERE p_id =".$sort_id;
            }
            $result = $this->querylist($sql);

            if ($result){
                foreach ($result as $key=>$val){
                    $ids .= ','.$val['department_id'].",";
                    $ids .= $this->getChildrenIds ($val['department_id']);
                }
            }

            $idsArr = explode(",",$ids);
            $idsArr = array_unique($idsArr);
            $ids = implode(",",$idsArr);
            return $ids;
        }

}